{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 操作数据库"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 创建表\n",
    "### 设置主键\n",
    "```SQL\n",
    "create table example0(stu_id int primary key, stu_name varchar(20), stu_sex boolean);\n",
    "\n",
    "create table example1(sut_id int, course_id int, grade float, primary key(stu_id, course_id));\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 设置表的外键\n",
    "```SQL\n",
    "create table example3(id int primary key, stu_id int, course_id int, constraint c_fk foreign key (stu_id, course_id) references exampls2(stu_id, course_id));\n",
    "```\n",
    "子表关联的必须是父表的主键，而且数据类型必须一致。\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 设置非空约束\n",
    "```SQL\n",
    "create table example4(id int not null primary key, name varchar(20) not null, stu_id int, constraint d_fk foreign key(stu_id) references example1(stu_id));\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 唯一性约束\n",
    "```SQL\n",
    "create table example5(id int primary key, stu_id int unique, name varchar(20) not null);\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 自增加属性\n",
    "- 作用：为新记录自动生成唯一的id\n",
    "- 一个表只有一个字段使用`auto_increment`约束\n",
    "- 该字段必须为主键的一部分\n",
    "- 可以约束任何整数类型\n",
    "- 默认从`1`开始\n",
    "```SQL\n",
    "create table example6(id int primary key auto_increment, stu_id int unique, name varchar(20) not null);\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 默认值设置\n",
    "```SQL\n",
    "create table example7(id int primary key auto_increment, stu_id int unique, name varchar(20) not null, english varchar(20) default 'zero', math float default 0, computer float default 0);\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 查看表结构\n",
    "### `Describe` 语句\n",
    "```SQL\n",
    "describe example1;\n",
    "desc example2;\n",
    "```\n",
    "### `show create table` 语句\n",
    "```SQL\n",
    "show create table example1 \\g;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 修改表\n",
    "### 修改表名\n",
    "`rename`\n",
    "```SQL\n",
    "alter table example0 rename user;\n",
    "```\n",
    "### 修改数据类型\n",
    "`modify`\n",
    "```SQL\n",
    "alter table user modify name varchar(30);\n",
    "```\n",
    "### 修改排列位置\n",
    "`modify`\n",
    "```MYSQL\n",
    "alter table user modify name varchar(30) first;  #第一位置\n",
    "alter table user modify sex tinyint(1) after age;\n",
    "```\n",
    "### 修改字段名\n",
    "`chagne`\n",
    "\n",
    "```MySQL\n",
    "alter table example1 change stu_name name varchar(20);  #不改变类型\n",
    "alter table example1 change stu_sex sex int(2);    #改变类型\n",
    "```\n",
    "### 增加字段\n",
    "`add` <br>为保证安全性，能加约束尽量加约束\n",
    "```Mysql\n",
    "alter table user add phone varchar(20);  #无完整约束\n",
    "alter table user add age int(4) not null;   #有约束\n",
    "alter table user add num int(8) primary key first; #第一位置增加\n",
    "alter table user add address varchar(20) not null after phone; #指定位置后增加\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 删除字段\n",
    "`drop`\n",
    "```MYSQL\n",
    "alter table user drop id;\n",
    "```\n",
    "### 更改存储引擎\n",
    "```MYSQL\n",
    "alter table user engine=myisam;\n",
    "```\n",
    "更改存储引擎，会带来额外问题，不要轻易更改`engine`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 删除表的外键约束\n",
    "`drop foreign key`\n",
    "```MYSQL\n",
    "alter table example3 drop foreign key c_fk;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 删除表\n",
    "### 删除普通表\n",
    "```MYSQL\n",
    "drop table example5;\n",
    "```\n",
    "### 删除被关联的父表\n",
    "```MYSQL\n",
    "alter table example4 drop foreign key d_fk; #先删除子表的外键约束\n",
    "drop table example1;  #再删除父表\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 本章实例\n",
    "```MYSQL\n",
    "use school;\n",
    "create table student(num int(10) primary key not null unique, name varchar(20) not null, sex varchar(4) not null, birthday datetime, address varchar(50));\n",
    "\n",
    "create table grade(id int(10) primary key not null unique auto_increment, course varchar(10) not null, s_num int(10) not null, grade varchar(4), constraint c_fk foreign key (s_num) references student(num));\n",
    "\n",
    "alter table grade modify course varchar(20);\n",
    "alter table grade modify s_num int(10) after id;\n",
    "alter table grade change grade score varchar(4); \n",
    "alter table grade drop foreign key c_fk;\n",
    "alter table grade engine=myisam;\n",
    "alter table student drop address;\n",
    "alter table student add phone int(10);\n",
    "alter table grade rename gradeInfo;\n",
    "drop table student;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.3"
  },
  "toc": {
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "toc_cell": false,
   "toc_position": {
    "height": "679px",
    "left": "0px",
    "right": "1388px",
    "top": "107px",
    "width": "212px"
   },
   "toc_section_display": "block",
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
